import requests
from bs4 import BeautifulSoup
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser


# 配置文件读取
def read_db_config(filename='db.config.ini', section='postgresql'):
    """ 读取数据库配置文件，返回一个字典对象
    """
    # 创建解析器，读取配置文件
    parser = ConfigParser()
    parser.read(filename)
    # 获取 postgresql 部分的配置
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception(' 文件{1}中未找到{0}配置信息！ '.format(section, filename))
    return db


db_config = read_db_config()
connection = None

try:
    # 使用 psycopg2.connect 方法连接 PostgreSQL 数据库
    connection = psycopg2.connect(**db_config)
    # 创建一个游标
    cur = connection.cursor()
    # 定义 SQL 语句
    sql = """ 
        INSERT INTO proxy_pool values (%s, %s, %s, %s, %s, %s, %s) ON CONFLICT (ip, port) DO NOTHING
    """

    for pageIndex in range(1, 4740):
        # 请求网页
        response = requests.get(url=f'https://free.kuaidaili.com/free/inha/{pageIndex}/')
        response.encoding = 'UTF-8'
        html = response.text

        doc = BeautifulSoup(html, 'lxml')

        trList = doc.find_all('tr')
        # 移除第一个元素
        del (trList[0])
        for tr in trList:
            # print(tr)
            proxyObj = {
                'ip': tr.find_all(name='td', attrs={'data-title': 'IP'})[0].text,
                'port': tr.find_all(name='td', attrs={'data-title': 'PORT'})[0].text,
                'anonymousLevel': tr.find_all(name='td', attrs={'data-title': '匿名度'})[0].text,
                'protocolType': tr.find_all(name='td', attrs={'data-title': '类型'})[0].text,
                'location': tr.find_all(name='td', attrs={'data-title': '位置'})[0].text,
                'responseDuration': tr.find_all(name='td', attrs={'data-title': '响应速度'})[0].text,
                'lastValidDate': tr.find_all(name='td', attrs={'data-title': '最后验证时间'})[0].text
            }
            print(proxyObj)
            # 执行 SQL 命令
            cur.execute(sql, (
                proxyObj['ip'],
                proxyObj['port'],
                proxyObj['anonymousLevel'],
                proxyObj['protocolType'],
                proxyObj['location'],
                proxyObj['responseDuration'],
                proxyObj['lastValidDate']
            ))

            # 获取 id
            # id = cur.fetchone()[0]
            # 提交事务
            connection.commit()
            # print("操作成功！ 用户 id：", id)

    # 关闭游标
    cur.close()
except (Exception, DatabaseError) as e:
    print("操作失败：", e)
finally:
    # 释放数据库连接
    if connection is not None:
        connection.close()
        print("PostgreSQL 数据库连接已关闭。")

# response = requests.get(url='https://free.kuaidaili.com/free/inha/1/')
# response.encoding = 'UTF-8'
# # print(response.text)
# # print(response.status_code)
# # print(response.headers)
# # print(response.url)
#
# doc = BeautifulSoup(response.text, 'lxml')
#
# trList = doc.find_all('tr')
# # 移除第一个元素
# del (trList[0])
#
# for tr in trList:
#     # print(tr)
#     proxyObj = {
#         'ip': tr.find_all(name='td', attrs={'data-title': 'IP'})[0].text,
#         'port': tr.find_all(name='td', attrs={'data-title': 'PORT'})[0].text,
#         'anonymousLevel': tr.find_all(name='td', attrs={'data-title': '匿名度'})[0].text,
#         'protocolType': tr.find_all(name='td', attrs={'data-title': '类型'})[0].text,
#         'location': tr.find_all(name='td', attrs={'data-title': '位置'})[0].text,
#         'responseDuration': tr.find_all(name='td', attrs={'data-title': '响应速度'})[0].text,
#         'lastValidDate': tr.find_all(name='td', attrs={'data-title': '最后验证时间'})[0].text
#     }
#     print(proxyObj)
